package cn.jhc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.jhc.bean.MusicBean;
import cn.jhc.bean.ShopBean;
import cn.jhc.bean.UserBean;

public class TitleDAO {

	private Connection con = null;
	int Integral,PageCount;

	public UserBean findUser(String username, String password) {
		String sql = "select id,name,password,displayname,integral from user " + "where name=? "
				+ " and password=password(?)";
		PreparedStatement stat = null;
		ResultSet result = null;
		UserBean user = null;
		try {
			stat = con.prepareStatement(sql);
			stat.setString(1, username);
			stat.setString(2, password);
			result = stat.executeQuery();
			if (result.next()) {
				user = new UserBean();
				user.setId(result.getInt(1));
				user.setName(result.getString(2));
				user.setPassword(result.getString(3));
				user.setDisplayname(result.getString(4));
				user.setIntegral(result.getInt(5));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return user;
	}

	public UserBean findAdminUser(String username, String password) {
		String sql = "select id,name,password from adminuser "
				+ "where name=? " + " and password=password(?)";
		PreparedStatement stat = null;
		ResultSet result = null;
		UserBean user = null;
		try {
			stat = con.prepareStatement(sql);
			stat.setString(1, username);
			stat.setString(2, password);
			result = stat.executeQuery();
			if (result.next()) {
				user = new UserBean();
				user.setId(result.getInt(1));
				user.setName(result.getString(2));
				user.setPassword(result.getString(3));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return user;
	}

	
	public void addOrder(String name, String college, String classname,
			String chinesename, String email, String phone, String title,
			String amcount, String subtotal) {
		String sql = "insert into userOrder(name,college,classname,chinesename,email,phone,title,amcount,subtotal,date) "
				+ "values(?,?,?,?,?,?,?,?,?,now())";
		PreparedStatement st = null;
		try {
			con = ConnectionFactory.getConnection();
			st = con.prepareStatement(sql);
			st.setString(1, name);
			st.setString(2, college);
			st.setString(3, classname);
			st.setString(4, chinesename);
			st.setString(5, email);
			st.setString(6, phone);
			st.setString(7, title);
			st.setString(8, amcount);
			st.setString(9, subtotal);
			st.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public int showIntegral(int userid){
		String sql="select integral from user where id=?";
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, userid);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				this.Integral=(result.getInt(1));
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return this.Integral;
	}
	public void addIntegral(int integral,int userid){
		int Integral = integral+3;
		String sql="update user set integral=? where id=?";
		PreparedStatement st = null;
		try {
			con = ConnectionFactory.getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, Integral);
			st.setInt(2, userid);
			st.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void addlittleIntegral(int integral,int userid){
		int Integral = integral+1;
		String sql="update user set integral=? where id=?";
		PreparedStatement st = null;
		try {
			con = ConnectionFactory.getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, Integral);
			st.setInt(2, userid);
			st.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void updateIntegral(int integral,int userid){
		String sql="update user set integral=? where id=?";
		PreparedStatement st = null;
		try {
			con = ConnectionFactory.getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, integral);
			st.setInt(2, userid);
			st.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void MinusIntegral(int integral,int userid){
		String sql="update user set integral=? where id=?";
		PreparedStatement st = null;
		try {
			con = ConnectionFactory.getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, integral);
			st.setInt(2, userid);
			st.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public ArrayList<MusicBean> SearchMusic(String title) {
		ArrayList<MusicBean> list = new ArrayList<MusicBean>();
		String sql = "select id,imagename,title from uploadfile where title like '%" + title + "%' order by id desc";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);			
			while (result.next()) {
				MusicBean u = new MusicBean();
				u.setId(result.getInt(1));
				u.setImagename(result.getString(2));
				u.setTitle(result.getString(3));
				list.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public void updateNeedIntegral(int integral,int userid){
		String sql="update uploadfile set integral=? where id=?";
		PreparedStatement st = null;
		try {
			con = ConnectionFactory.getConnection();
			st = con.prepareStatement(sql);
			st.setInt(1, integral);
			st.setInt(2, userid);
			st.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public int getMusicCount(int titleCount) {
		String sql = "select count(*) from uploadfile";	
		PreparedStatement ps = null;
		ResultSet result = null;
		try {
			ps = con.prepareStatement(sql);
			result = ps.executeQuery();
			if (!result.next())
				result = null;
			else {
				if(result.getInt(1)%titleCount==0){
					this.PageCount=(result.getInt(1)/titleCount);
				}else{
					this.PageCount=(result.getInt(1)/titleCount+1);	
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return PageCount ;
	}
	public ArrayList<MusicBean> getAllMusic(int page,int titleCount) {
		ArrayList<MusicBean> list = new ArrayList<MusicBean>();
		String sql = "select id ,imagename,title from uploadfile order by id desc limit "
				+ (page-1) * (titleCount) + "," + titleCount + "";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);			
			while (result.next()) {
				MusicBean u = new MusicBean();
				u.setId(result.getInt(1));
				u.setImagename(result.getString(2));
				u.setTitle(result.getString(3));
				list.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	public MusicBean getOneMusic(int id){
		MusicBean m = new MusicBean();
		String sql = "select id,imagename,musicname,title,content,integral from uploadfile where id="+id+"";
		try {
			Statement stat = con.createStatement();
			ResultSet result = stat.executeQuery(sql);	
			if (!result.next())
				m = null;
			else {
				m.setId(result.getInt(1));
				m.setImagename(result.getString(2));
				m.setMusicname(result.getString(3));
				m.setTitle(result.getString(4));
				m.setContent(result.getString(5));
				m.setIntegral(result.getInt(6));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return m;
	}
	public void setCon(Connection con) {
		this.con = con;
	}
}
